﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace VWise_DAO
{

  public class CDBAccess : IDisposable
  {
    public const int W_OK = 0;
    public const int W_FAIL = -1;

    protected bool bconnected;
    public string szConectionString;

    protected SqlConnection sConn = new SqlConnection();

    public bool bConnected
    {
      get { return bconnected; }
    }


    public void Dispose()
    {
      this.Disconnect();
    }

    public CDBAccess(string szConString)
    {
      bconnected = false;
      szConectionString = szConString;
    }

    public CDBAccess()
    {
      bconnected = false;
      szConectionString = "";
    }

    public bool Connect()
    {
      try
      {
        sConn.ConnectionString = szConectionString;
        sConn.Open();
        bconnected = true;
      }
      catch (SqlException ex)
      {
        CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR,"Fail in connect: " + ex.ToString());
        bconnected = false;
      }
      finally
      {
        if (!bconnected)
        {
          sConn.Close();
        }
      }

      return bconnected;
    }

    public void Disconnect()
    {
      if (bconnected)
      {
        bconnected = false;
        try
        {
          sConn.Close();
        }
        catch (SqlException ex)
        {
          //upss
          CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR,"Failed to exec Disconnect: " + ex.ToString());
          bconnected = true;
        }
      }
    }

    public void LaunchSQLNonQuery(string strSQLline)
    {
      SqlCommand cmdConsulta = new SqlCommand();
      cmdConsulta.CommandText = strSQLline;
      cmdConsulta.CommandType = CommandType.Text;
      cmdConsulta.Connection = this.sConn;

      try
      {
        cmdConsulta.ExecuteNonQuery();
      }
      catch (SqlException ex)
      {
        CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR,"Failed to exec query: " + ex.ToString());
      }
    }

    public int LaunchSQLStoreProcedure(string szSQLSPName, Dictionary<string,object> pDictionary,out SqlDataReader pDataReader)
    {
      SqlCommand cmdConsulta = new SqlCommand();
      int ires;

      cmdConsulta.CommandText = szSQLSPName;
      cmdConsulta.CommandType = CommandType.StoredProcedure;
      cmdConsulta.Connection = this.sConn;

      pDataReader = null; 
      
      if (pDictionary == null)
        return W_FAIL;

      try
      {
        foreach (KeyValuePair<string, object> kPair in pDictionary)
        {
          cmdConsulta.Parameters.AddWithValue(kPair.Key, kPair.Value);
        }
        pDataReader = cmdConsulta.ExecuteReader();
        ires = W_OK;
      }
      catch (SqlException sqlex)
      {
        CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR,"Failed to exec SP Datareader: " + sqlex.ToString());
        ires = W_FAIL;
      }
      
      return ires;
    }

    public int LaunchSQLStoreProcedure(string szSQLSPName, Dictionary<string, object> pDictionary,out int iRowsAffected)
    {
      SqlCommand cmdConsulta = new SqlCommand();
      int ires;

      cmdConsulta.CommandText = szSQLSPName;
      cmdConsulta.CommandType = CommandType.StoredProcedure;
      cmdConsulta.Connection = this.sConn;

      iRowsAffected = 0;
      if (pDictionary == null)
        return W_FAIL;

      try
      {
        foreach (KeyValuePair<string, object> kPair in pDictionary)
        {
          cmdConsulta.Parameters.AddWithValue(kPair.Key, kPair.Value);
        }

        iRowsAffected = cmdConsulta.ExecuteNonQuery();
        ires = W_OK;
      }
      catch (SqlException sqlex)
      {
        CLogProcessor.LogInfo(CLogProcessor.W_LOG_ERROR,"Failed to exec SP non query: " + sqlex.ToString());
        ires = W_FAIL;
      }

      return ires;
    }
  }
}
